﻿using Dapper.Library;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace Dapper.MySql.Library
{
    /// <summary>
    /// MySql提供者
    /// </summary>
    public class MySqlProvider : SqlProvider
    {
        /// <summary>
        /// 参数信息
        /// </summary>
        private DynamicParameters parameters = null;

        #region SQL拼装信息

        //执行语句
        string executeSql = string.Empty;
        //topN
        int? topNum;
        //表名称
        string fromTableSql = string.Empty;
        //无锁SQL
        string nolockSql = string.Empty;
        //参数
        WhereExpression whereParams = null;
        //条件语句
        string whereSql = string.Empty;
        //排序语句
        string orderbySql = string.Empty;

        #endregion

        /// <summary>
        /// 分页用到的sql语句
        /// </summary>
        private const string PAGINATION_SQL = @"SELECT	COUNT (1) FROM ({0}) ForMaxCount;--最大条数
                                                SELECT	* FROM( {0} ) AS query {1} LIMIT {2},{3};";
        /// <summary>
        /// 开始符号
        /// </summary>
        private const char OpenQuote = '`';

        /// <summary>
        /// 结束符号
        /// </summary>
        private const char CloseQuote = '`';

        /// <summary>
        /// 匹配符前缀
        /// </summary>
        private const char ParameterPrefix = '@';

        public MySqlProvider()
        {
            SymbolInfo = new SymbolInfo(OpenQuote, CloseQuote, ParameterPrefix);
            ResolveExpression.InitOption(SymbolInfo);
        }

        protected sealed override SymbolInfo SymbolInfo { get; set; }


        /// <summary>
        /// 拼装单条查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public override SqlProvider FormatToSingle<T>()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, 1);
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;
                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql}";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                executeSql = SetContext.SqlString;
                Params = parameters;
                SqlString = $"{executeSql}";
            }
            return this;
        }

        /// <summary>
        /// 拼装多条查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public override SqlProvider FormatToList<T>()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                topNum = SetContext.TopNum;
                executeSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, topNum);
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;
                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql}";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                executeSql = SetContext.SqlString;
                Params = parameters;
                SqlString = $"{executeSql}";
            }
            return this;
        }

        /// <summary>
        /// 拼装分页查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public override SqlProvider FormatToPageList<T>(int pageIndex, int pageSize)
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);
                if (string.IsNullOrEmpty(orderbySql))
                {
                    throw new Exception("排序方式优先于页列表");
                }

                executeSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, pageSize);
                fromTableSql = FormatTableName();
                //nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;

                SqlString = $"SELECT COUNT(1) {fromTableSql} {whereSql};";
                SqlString += $@"{executeSql}
                           FROM ( SELECT *
                                  {fromTableSql}
                                  {whereSql}
                                ) T
                       {orderbySql}  LIMIT {(pageIndex - 1)},{pageSize} ;";
            }
            else
            {

                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);
                if (string.IsNullOrEmpty(orderbySql)) {
                    throw new Exception("排序方式优先于页列表");
                }

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                executeSql = SetContext.SqlString;
                Params = parameters;
                //分页查询语句
                SqlString = string.Format(PAGINATION_SQL,
                                            executeSql,
                                            orderbySql,
                                            (pageIndex - 1),
                                            pageSize);
            }

            return this;
        }

        /// <summary>
        /// 获取COUNT()函数SQL
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatCount()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = "SELECT COUNT(1)";
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} ";
            }
            else
            {

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);
                executeSql = $"SELECT COUNT(1) FROM ({SetContext.SqlString}) ForCount";
                Params = parameters;

                SqlString = $"{executeSql}";
            }
            return this;
        }

        /// <summary>
        /// 获取Exists()函数SQL
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatExists()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = "SELECT COUNT(1) ";
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql}";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);
                executeSql = $"SELECT COUNT(1) FROM ({SetContext.SqlString}) FormatExists";
                Params = parameters;

                SqlString = $"{executeSql}";
            }

            return this;
        }

        /// <summary>
        ///  获取DeleteSQL 0nosql 1sql
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatDelete()
        {
            fromTableSql = FormatTableName();
            whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
            whereSql = whereParams.SqlCmd;
            Params = whereParams.Param;

            SqlString = $"DELETE {fromTableSql} {whereSql}";

            return this;
        }

        /// <summary>
        /// 获取ExecuteNoQuerySQ
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatExecuteNoQuery()
        {

            parameters = new DynamicParameters();
            parameters.AddDynamicParams(SetContext.Params);
            executeSql = SetContext.SqlString;
            Params = parameters;

            SqlString = $"{executeSql}";

            return this;
        }

        /// <summary>
        /// 获取Insert-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public override SqlProvider FormatInsert<T>(T entity)
        {
            var paramsAndValuesSql = FormatInsertParamsAndValues(entity);

            if (SetContext.IfNotExistsExpression == null)
                SqlString = $"INSERT INTO {FormatTableName(false)} ({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]})";
            else
            {
                var ifnotexistsWhere = ResolveExpression.ResolveWhere(SetContext.IfNotExistsExpression, "INT_");

                SqlString = $"INSERT INTO {FormatTableName(false)} ({paramsAndValuesSql[0]}) SELECT {paramsAndValuesSql[1]} FROM DUAL " +
                    $" WHERE NOT EXISTS (SELECT 1 FROM { FormatTableName(false)} {ifnotexistsWhere.SqlCmd}) ";

                Params.AddDynamicParams(ifnotexistsWhere.Param);
            }
            return this;
        }

        /// <summary>
        /// 获取Update-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateExpression"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdate<T>(Expression<Func<T, T>> updateExpression)
        {
            if (SetContext.IfNotExistsExpression == null) {

                var update = ResolveExpression.ResolveUpdate(updateExpression);
                var where = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                var whereSql = where.SqlCmd;

                Params = where.Param;
                Params.AddDynamicParams(update.Param);

                SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
            }
            else
            {
                StringBuilder resultInfo = new StringBuilder();

                var update = ResolveExpression.ResolveUpdate(updateExpression, "UPDATE_TAB");
                var ifnotexistsWhere = ResolveExpression.ResolveWhere(SetContext.IfNotExistsExpression, "UPDATE_");
                var whereSql = ifnotexistsWhere.SqlCmd;

                string sqlCmd = ifnotexistsWhere.SqlCmd.Replace("UPDATE_gCode", "gCode");
                string spliceSql = string.IsNullOrEmpty(whereSql) ? " WHERE " : "AND";

                //UPDATE  `SysPost`  AS UPDATE_TAB
                //LEFT JOIN
                //(
                //  SELECT* FROM  `SysPost`   WHERE `gCode` != 'C893D831163DC6C9228608D756F987AD' AND `sName` = '普通员工'
                //) AS NOT_EXISTS
                //ON NOT_EXISTS.gCode = UPDATE_TAB.gCode
                //SET UPDATE_TAB.`sName`= '普通员工' , 
                //		UPDATE_TAB.`sBrevityCode`= 'USER' , 
                //		UPDATE_TAB.`iSort`= 7 , 
                //		UPDATE_TAB.`sContent`= '普通员工' , 
                //		UPDATE_TAB.`bIsDeleted`= false
                //WHERE UPDATE_TAB.`gCode` = 'C893D831163DC6C9228608D756F987AD' AND NOT_EXISTS.gCode is null

                resultInfo.Append($"UPDATE {FormatTableName(false)} AS  UPDATE_TAB ");
                resultInfo.Append($"LEFT JOIN ( SELECT * FROM { FormatTableName(false)} {sqlCmd} ) AS NOT_EXISTS ");
                resultInfo.Append($"ON NOT_EXISTS.gCode=UPDATE_TAB.gCode ");
                resultInfo.Append($"{update.SqlCmd} {whereSql} {spliceSql} NOT_EXISTS.gCode is null");
                SqlString = resultInfo.ToString();

            }
            return this;

        }

        /// <summary>
        /// 获取Update-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdate<T>(T entity)
        {
            if (SetContext.IfNotExistsExpression == null)
            {
                var update = ResolveExpression.ResolveUpdate<T>(a => entity);
                var where = ResolveExpression.ResolveWhere(entity);
                var whereSql = where.SqlCmd;

                Params = where.Param;
                Params.AddDynamicParams(update.Param);

                SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
            }
            else
            {
                StringBuilder resultInfo = new StringBuilder();

                var update = ResolveExpression.ResolveUpdate<T>(a => entity, "UPDATE_TAB");
                var where = ResolveExpression.ResolveWhereNotExists(entity, "UPDATE_TAB");
                var whereSql = where.SqlCmd;

                Params = where.Param;
                Params.AddDynamicParams(update.Param);

                var ifnotexistsWhere = ResolveExpression.ResolveWhere(SetContext.IfNotExistsExpression, "UPDATE_");
                string sqlCmd = ifnotexistsWhere.SqlCmd.Replace("UPDATE_gCode", "gCode");
                string spliceSql = string.IsNullOrEmpty(whereSql) ? " WHERE " : "AND";

                resultInfo.Append($"UPDATE {FormatTableName(false)} AS  UPDATE_TAB ");
                resultInfo.Append($"LEFT JOIN ( SELECT * FROM { FormatTableName(false)} {sqlCmd} ) AS NOT_EXISTS ");
                resultInfo.Append($"ON NOT_EXISTS.gCode=UPDATE_TAB.gCode ");
                resultInfo.Append($"{update.SqlCmd} {whereSql} {spliceSql} NOT_EXISTS.gCode is null");
                SqlString = resultInfo.ToString();

            }
            return this;
        }

        /// <summary>
        /// 获取Sum-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lambdaExpression"></param>
        /// <returns></returns>
        public override SqlProvider FormatSum<T>(LambdaExpression lambdaExpression)
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = ResolveExpression.ResolveSum(typeof(T).GetProperties(), lambdaExpression);
                fromTableSql = FormatTableName();
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;
                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} ";
            }
            else
            {

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                Params = parameters;
                executeSql = ResolveExpression.ResolveSum(typeof(T).GetProperties(), lambdaExpression);
                SqlString = $"{executeSql} FROM ({SetContext.SqlString}) FormatSum";
            }

            return this;
        }

        /// <summary>
        /// 获取先Update-再Select-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updator"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdateSelect<T>(Expression<Func<T, T>> updator)
        {
            var update = ResolveExpression.ResolveUpdate(updator);

            var executeSql = ResolveExpression.ResolveSelectOfUpdate(typeof(T).GetProperties(), SetContext.SelectExpression);

            var where = ResolveExpression.ResolveWhere(SetContext.WhereExpression);

            var whereSql = where.SqlCmd;

            Params = where.Param;
            Params.AddDynamicParams(update.Param);

            //var topNum = SetContext.TopNum;
            //var topSql = topNum.HasValue ? $" TOP ({topNum.Value})" : "";

            SqlString = $"UPDATE  {FormatTableName(false)} {update.SqlCmd} {whereSql};" +
                $"SELECT {executeSql} FROM {FormatTableName(false)} {whereSql}";

            return this;
        }

        /// <summary>
        /// 获取批量插入-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public override SqlProvider ExcuteBulkCopy<T>(IDbConnection conn, IEnumerable<T> list)
        {
            var dt = list.ToDataTable();
            MySqlTransaction tran = null;
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                tran = ((MySqlConnection)conn).BeginTransaction();
                var sqlbulkcopy = new MySqlBulkLoader((MySqlConnection)conn);
                sqlbulkcopy.TableName = dt.TableName;
                sqlbulkcopy.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                sqlbulkcopy.Load();

                tran.Commit();
            }
            catch (MySqlException ex)
            {
                if (tran != null) tran.Rollback();
                throw ex;
            }
            return this;
        }
    }
}
